package com.xoa.controller.workflow;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.MapMaker;
import com.xoa.controller.users.UsersController;
import com.xoa.model.dataSrc.DataSrc;
import com.xoa.model.users.Users;
import com.xoa.model.workflow.TerpServer;
import com.xoa.service.users.UsersService;
import com.xoa.service.workflow.TerpServer.TerpServerService;
import com.xoa.util.CookiesUtil;
import com.xoa.util.ThredQuery;
import com.xoa.util.ToJson;
import com.xoa.util.common.StringUtils;
import com.xoa.util.common.session.SessionUtils;
import com.xoa.util.page.PageParams;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;


@Controller
@RequestMapping(value = "/TerpServerController")
public class TerpServerController {

    @Autowired
    private TerpServerService terpServerService;

    @Autowired
    private  UsersService usersService;

    /*
     * 创建作者:   廉立深
     * 创建日期:   19:39 2019/12/12
     * 方法介绍:   页签头映射
     * 参数说明:   []
     * @return     java.lang.String
     */
    @RequestMapping("terpserverIndex")
    public String terpserverIndex(){
        return "/app/workflow/terpserver/terpserverIndex";
    }


    /*
     * 创建作者:   廉立深
     * 创建日期:   20:25 2019/12/12
     * 方法介绍:   内部数据源映射
     * 参数说明:   []
     * @return     java.lang.String
     */
    @RequestMapping("internalDataSource")
    public String internalDataSource(){
        return "/app/workflow/terpserver/internalDataSource";
    }


    /*
     * 创建作者:   廉立深
     * 创建日期:   20:26 2019/12/12
     * 方法介绍:   外部数据源映射
     * 参数说明:   []
     * @return     java.lang.String
     */
    @RequestMapping("ExternalDataSource")
    public String ExternalDataSource(){
        return "/app/workflow/terpserver/ExternalDataSource";
    }


    /*
     * 创建作者:   廉立深
     * 创建日期:   13:37 2019/12/23
     * 方法介绍:   浏览 添加 编辑 映射页面
     * 参数说明:   []
     * @return     java.lang.String
     */
    @RequestMapping("/terpflowdata")
    public String terpflowdata(){
        return "/app/workflow/terpserver/terpflowdata";
    }

    /*
     * 创建作者:   廉立深
     * 创建日期:   10:23 2020/1/2
     * 方法介绍:     表单外部控件树结构
     * 参数说明:   []
     * @return     java.lang.String
     */
    @RequestMapping("/dataSelectTree")
    public String dataSelectTree(){
        return "/app/workflow/terpserver/dataSelectTree";
    }

    /*
     * 创建作者:   廉立深
     * 创建日期:   14:47 2020/1/3
     * 方法介绍:   表单预览外部数据控件表格映射
     * 参数说明:   []
     * @return     java.lang.String
     */
    @RequestMapping("/dataSelectPreview")
    public String dataSelectPreview(){
        return "/app/workflow/terpserver/dataSelectPreview";
    }

    /*
     * 创建作者:   廉立深
     * 创建日期:   15:40 2020/1/9
     * 方法介绍:   移动端表单预览外部数据控件表格映射
     * 参数说明:   []
     * @return     java.lang.String
     */
    @RequestMapping("/dataSelectPreviewApp")
    public String dataSelectPreviewApp(){
        return "/app/workflow/terpserver/dataSelectPreviewApp";
    }


    /**
     * 内部数据源表单预览
     * @return
     */
    @RequestMapping("/dataSelect")
    public String dataSelect(){
        return "/app/workflow/terpserver/dataSelect";
    }



    //定义MySQL的数据库驱动程序
    private static final String forName="com.mysql.jdbc.Driver";
    private static final String forName2="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String forName3="oracle.jdbc.driver.OracleDriver";


    /*
     * 创建作者:   廉立深
     * 创建日期:   10:17 2020/1/13
     * 方法介绍:   预览表单里的表格接口
     * 参数说明:   [terpServer, page, limit]
     * @return     com.xoa.util.ToJson
     */
    @RequestMapping(value = "/selectTerpServer")
    @ResponseBody
    public ToJson selectTerpServer(TerpServer terp,
                                   @RequestParam(value = "page" ,required = false) Integer page,
                                   @RequestParam(value = "limit",required = false) Integer limit) {
        ToJson toJson=new ToJson(1,"err");
        try {
            TerpServer terpServer=terpServerService.selectTerpById(terp.getServerId());

            ThredQuery d1=null; //线程链接类
            int sqlindex = 0;

            Map sqlMap = new HashMap();
            sqlMap.put(++sqlindex ,terp.getTableName());
            String sql="select * from ? "; //sql语句，默认部分页查询全部

            if (terpServer!=null){
                switch (terpServer.getDbmsType()) {
                    case "MYSQL":

                        if (!StringUtils.checkNull(terp.getStrjson())){
                            sql+="  where 1=1 ";
                            Map<String,Object> maps = (Map)JSON.parse(terp.getStrjson());
                            for (Map.Entry<String,Object> map:maps.entrySet()) {
                                if (map.getValue()!=null && !map.getValue().equals("")) {
                                        sqlMap.put( ++sqlindex , map.getKey() );
                                        sqlMap.put( ++sqlindex , "'%" + map.getValue() + "%'" );
                                    sql += " and  ?  like  ? ";
                                }
                            }
                        }

                        //分页处理
                        if(page!=null && limit!=null){
                            sqlMap.put( ++sqlindex , (page-1)*page );
                            sqlMap.put( ++sqlindex , limit);
                            sql+=" LIMIT ? , ?";
                        }

                        d1 = new ThredQuery(
                                forName,
                                "jdbc:mysql://"+terpServer.getDbServerIp()+":"+terpServer.getDbServerPort()+"/"+terp.getDbDataBaseName()+"?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true",
                                sql,terpServer.getDbServerUser(),
                                terpServer.getDbServerPass(),
                                sqlMap
                        );

                        break;
                    case "SQL SERVER":

                        /*if(page!=null && limit!=null){
                            sqlindex = 0;

                            sqlMap.put( ++sqlindex , ((page)*limit) );
                            sqlMap.put( ++sqlindex , terp.getTableName() );
                            sql="select top ? * from ? ";

                        }*/

                        /*if (!StringUtils.checkNull(terp.getStrjson())){
                            sql+="  where 1=1 ";
                            Map<String,Object> maps = (Map)JSON.parse(terp.getStrjson());
                            for (Map.Entry<String,Object> map:maps.entrySet()) {
                                if (map.getValue()!=null && !map.getValue().equals("")) {
                                    sqlMap.put(++sqlindex, map.getKey());
                                    sqlMap.put(++sqlindex, "'%" + map.getValue() + "%'");
                                    sql += " and  ?  like  ? ";
                                }
                            }
                        }*/

                        d1 = new ThredQuery(
                                forName2,
                                "jdbc:sqlserver://"+terpServer.getDbServerIp()+":"+terpServer.getDbServerPort()+";databaseName="+terpServer.getDbDataBaseName()+";",
                                "select * from " + terp.getTableName(),
                                terpServer.getDbServerUser(),
                                terpServer.getDbServerPass(),
                                new HashMap()
                        );
                        break;
                    case "ORACLE":

                        if(page!=null && limit!=null){
                            sqlindex = 0;
                            sqlMap.put( ++sqlindex , terp.getTableName() );
                            sqlMap.put( ++sqlindex , page );
                            sqlMap.put( ++sqlindex , limit );

                            sql= "SELECT * FROM ( SELECT temp.*,ROWNUM RN  FROM (SELECT * FROM \"?\") temp) WHERE RN BETWEEN  ?  AND  ?";
                        }
                        if (!StringUtils.checkNull(terp.getStrjson())){
                            Map<String,Object> maps = (Map)JSON.parse(terp.getStrjson());
                            for (Map.Entry<String,Object> map:maps.entrySet()) {
                                if (map.getValue() != null && !map.getValue().equals("")) {

                                    sqlMap.put( ++sqlindex , map.getKey() );
                                    sqlMap.put( ++sqlindex , "'%" + map.getValue() + "%'" );

                                    sql += " and \"?\"  like  ?";

                                }
                            }
                        }

                        d1 = new ThredQuery(
                                forName3,
                                "jdbc:oracle:thin:@"+terpServer.getDbServerIp()+":"+terpServer.getDbServerPort()+":"+terpServer.getDbDataBaseName(),
                                sql,
                                terpServer.getDbServerUser(),
                                terpServer.getDbServerPass(),
                                sqlMap
                        );
                        break;
                }

                Thread t1 = new Thread(d1);
                t1.start();
                Vector<Hashtable<String, Object>> vhso = d1.getVhso();
                try {
                    if (t1!=null){
                        t1.join();
                    }
                } catch (InterruptedException e1) {
                    e1.printStackTrace();
                }


                toJson.setObj(vhso);
                toJson.setTotleNum(vhso.size());
                toJson.setMsg("true");
                toJson.setFlag(0);
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }



    /*
     * 创建作者:   廉立深
     * 创建日期:   14:12 2020/1/10
     * 方法介绍:   移动端查询表信息he 内部数据源信息
     * 参数说明:   [dName:表名，strjson：条件]
     * @return     com.xoa.util.ToJson
     */
    @RequestMapping("/selectapptable")
    @ResponseBody
    public ToJson selectapptable(HttpServletRequest request,
                                 @RequestParam(value = "dName") String dName,
                                 @RequestParam(value = "strjson",required = false) String strjson,
                                 @RequestParam(value = "page" ,required = false) Integer page,
                                 @RequestParam(value = "limit",required = false) Integer limit){
        ToJson toJson = new ToJson(1,"false");
        if(dName.equals("data_hr_staff_info")){
            dName="hr_staff_info";
        }

        Map map=new HashMap();
        try {
            PageParams pageParams=new PageParams();
            pageParams.setPageSize(limit);
            pageParams.setPage(page);
            pageParams.setUseFlag(true);
            map.put("pageParams",pageParams);
        }catch (Exception e){
            toJson.setMsg(e.getMessage());
        }
        try {
            String sqlType = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");


            //判断库里是否存在这张表
            String dbName = terpServerService.findDbName(sqlType, dName);
            if (!StringUtils.checkNull(dbName) && dbName.equals(dName)){
                map.put("tableName",dbName);
            }

            if (!StringUtils.checkNull(strjson)){
                Map maps = (Map)JSON.parse(strjson);
                Iterator<Map.Entry<String, String>> it = map.entrySet().iterator();
                while (it.hasNext()) {
                        Map.Entry<String, String> entry = it.next();
                    String dbTabName = terpServerService.findDbTabName(sqlType, dbName, (String) entry.getKey());
                    if (StringUtils.checkNull(dbTabName) || !dbTabName.equals((String) entry.getKey())){
                        maps.remove((String) entry.getKey());
                    }
                }
                map.put("condition",maps);
            }
            //公共查询表方法返回全部信息，传入表名
            List<Map<String,Object>> maps= terpServerService.selectAll(map);

            if (maps.size()>0){
                toJson.setObj(maps);
                PageParams pageParams=(PageParams)map.get("pageParams");
                toJson.setTotleNum(pageParams.getTotal());
                toJson.setFlag(0);
                toJson.setMsg("true");
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }


    /*
     * 创建作者:   廉立深
     * 创建日期:   16:23 2020/1/2
     * 方法介绍:   查询某个表的全部字段信息
     * 参数说明:   [request, tableName:传入要查询的表名,tableSchema：要查询的库,isapp:判断状态是否是移动端]
     * @return     com.xoa.util.ToJson
     */
    @RequestMapping("/findTableField")
    @ResponseBody
    public ToJson findTableField(HttpServletRequest request,
                                 @RequestParam("tableName") String tableName,
                                 @RequestParam("tableSchema") String tableSchema,
                                 @RequestParam("id") String id){
        ToJson toJson=new ToJson(1,"false");
        try {
            TerpServer terpServer = terpServerService.selectTerpById(Integer.parseInt(id));
            if (terpServer!=null){
                ThredQuery d1 = null;
                Map sqlMap = new HashMap();

                switch (terpServer.getDbmsType()) {
                    case "MYSQL":
                        sqlMap.put(1,tableName);
                        sqlMap.put(2,tableSchema);

                         d1 = new ThredQuery(
                                forName,
                                "jdbc:mysql://"+terpServer.getDbServerIp()+":"+terpServer.getDbServerPort()+"/"+terpServer.getDbDataBaseName()+"?useUnicode=true&characterEncoding="+terpServer.getDbDataBaseCharset()+"&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true",
                                "SELECT" +
                                        "      column_name AS data1," +
                                        "      data_type as data2," +
                                        "      column_comment AS data3," +
                                        "      column_key as data4," +
                                        "      table_schema as data5" +
                                        "    FROM" +
                                        "        information_schema.COLUMNS" +
                                        "    WHERE" +
                                        "      table_name = '?'"+
                                        "      AND table_schema = '?'",
                                terpServer.getDbServerUser(),
                                terpServer.getDbServerPass() ,
                                 sqlMap
                        );
                        break;
                    case "SQL SERVER":

                        sqlMap.put(1,tableName);
                        d1 = new ThredQuery(
                                forName2,
                                "jdbc:sqlserver://"+terpServer.getDbServerIp()+":"+terpServer.getDbServerPort()+";databaseName="+terpServer.getDbDataBaseName()+";",
                                "SELECT name FROM SysColumns WHERE ID=OBJECT_ID(?)",
                                terpServer.getDbServerUser(),
                                terpServer.getDbServerPass(),
                                sqlMap
                        );

                        break;
                    case "ORACLE":
                        sqlMap.put(1,tableName);
                        d1=new ThredQuery(
                                forName3,
                                "jdbc:oracle:thin:@"+terpServer.getDbServerIp()+":"+terpServer.getDbServerPort()+":"+terpServer.getDbDataBaseName(),
                                "SELECT column_name FROM user_tab_columns where table_name = '?' ",
                                terpServer.getDbServerUser(),
                                terpServer.getDbServerPass(),
                                sqlMap
                        );
                        break;
                }

                //进行连接
                Thread t1 = new Thread(d1);
                t1.start();
                Vector<Hashtable<String, Object>> vhso = d1.getVhso();
                try {
                    if (t1!=null){
                        t1.join();
                    }
                } catch (InterruptedException e1) {
                    e1.printStackTrace();
                }
                //返回状态
                toJson.setMsg("true");
                toJson.setFlag(0);
                toJson.setObj(vhso);
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    /**
     * 保存数据源
     * @param terpServer 数据源信息
     * @return
     */
    @RequestMapping("/insertTerpServer")
    @ResponseBody
    public ToJson insertTerpServer(TerpServer terpServer){
        ToJson toJson = new ToJson(1,"false");
        try {
            boolean bo;
            if (terpServer.getServerId()!=null){
                bo = terpServerService.updataTerpById(terpServer); //修改
            }else {
                bo = terpServerService.insertTerpServer(terpServer);  //新增
            }
            if(bo){
                toJson.setFlag(0);
                toJson.setMsg("ok");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return toJson;
    }

    /**
     * 查询所有数据源名称
     * @return
     */
    @RequestMapping(value = "/selectDbServerNameTerpServer")
    @ResponseBody
    public ToJson selectDbServerNameTerpServer(TerpServer terpServer){
        ToJson toJson = new ToJson(1,"false");
        try {
            List listmap=new ArrayList();
            List<TerpServer> list =terpServerService.selectDbServerNameTerpServer(terpServer);
            for(TerpServer terpServer1: list){
                Map map =new HashMap();
                map.put("id",terpServer1.getServerId());
                map.put("label",terpServer1.getDbServerName());
                map.put("isLeaf",false); //有子级为false，无子级为true
                map.put("pId","0");
                listmap.add(map);
            }
            if(list.size()>0){
                toJson.setObj(list);
                toJson.setObject(listmap); //储存表单的树结构
                toJson.setMsg("ok");
                toJson.setFlag(0);
                toJson.setCode("0"); //这个状态是通用判断状态
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }


    //查询库的所有表结构,传入实体只是为了判断状态
    @RequestMapping("/findtablAll")
    @ResponseBody
    public ToJson findtablAll(HttpServletRequest request,TerpServer terpServer,String pId){
        ToJson toJson=new ToJson(1,"false");
        Thread t1=null;
        try {
            List list =new ArrayList();
            List<TerpServer> terpServers = terpServerService.selectDbServerNameTerpServer(terpServer);
            if (terpServers.size()>0) {

                Map sqlMap = new HashMap();

                //获取第一条
                TerpServer terpServer1 = terpServers.get(0);

                //设置参数
                String root=terpServer1.getDbServerUser(); //用户名
                String pwd=terpServer1.getDbServerPass(); //密码



                //检查链接是否成功
                ToJson toJson1 = this.TerpServerConnectionTest(terpServer1);
                if (toJson1.isFlag()) {
                    if (pId != null && pId.equals("0")) {
                        if (!StringUtils.checkNull(terpServer1.getDbDataBaseName())){
                            Map map = new HashMap();
                            map.put("id", terpServer1.getServerId());
                            map.put("label", terpServer1.getDbDataBaseName());
                            map.put("isLeaf", false); //false有子级
                            list.add(map);
                        }else{

                            //创建对象
                            ThredQuery d1 = null;
                            switch (terpServer1.getDbmsType()){
                                case "MYSQL":
                                    d1 = new ThredQuery(
                                            forName,
                                            "jdbc:mysql://"+terpServer1.getDbServerIp()+":"+terpServer1.getDbServerPort()+"/?useUnicode=true&characterEncoding=" +terpServer1.getDbDataBaseCharset()+"&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true",
                                            "select schema_name from information_schema.schemata",
                                            root,
                                            pwd,
                                            sqlMap
                                    );
                                    break;
                                case "SQL SERVER":
                                    d1=new ThredQuery(
                                            forName2,
                                            "jdbc:sqlserver://"+terpServer1.getDbServerIp()+":"+terpServer1.getDbServerPort()+";databaseName="+terpServer1.getDbDataBaseName()+";",
                                            "SELECT name FROM Master..SysDatabases ORDER BY name",
                                            root,
                                            pwd,
                                            sqlMap
                                    );
                                    break;
                                case "ORACLE":
                                    d1=new ThredQuery(
                                            forName3,
                                            "jdbc:oracle:thin:@"+terpServer1.getDbServerIp()+":"+terpServer1.getDbServerPort()+":"+terpServer1.getDbDataBaseName(),
                                            "select t.table_name from user_tables t",
                                            root,
                                            pwd,
                                            sqlMap
                                    );
                                    break;
                            }

                            //执行
                            t1 = new Thread(d1);
                            t1.start();
                            Vector<Hashtable<String, Object>> vhso = d1.getVhso();
                            //关闭线程
                            try {
                                if (t1!=null){
                                    t1.join();
                                }
                            } catch (InterruptedException e1) {
                                e1.printStackTrace();
                            }
                            for (Map map : vhso) {
                                Map map2 = new HashMap();
                                map2.put("id", terpServer1.getServerId());
                                String SCHEMA_NAME=(String)map.get("SCHEMA_NAME");
                                String name=(String)map.get("name");
                                map2.put("label", SCHEMA_NAME != null ? SCHEMA_NAME : name);
                                map2.put("isLeaf", false); //false有子级
                                list.add(map2);
                            }
                        }
                    } else {

                        //创建对象
                        ThredQuery d1 = null;
                        switch (terpServer1.getDbmsType()){
                            case "MYSQL":
                                d1 = new ThredQuery(
                                        forName,
                                        "jdbc:mysql://"+terpServer1.getDbServerIp()+":"+terpServer1.getDbServerPort()+"/?useUnicode=true&characterEncoding=" +terpServer1.getDbDataBaseCharset()+"&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true",
                                        "select schema_name from information_schema.schemata",
                                        root,
                                        pwd,
                                        sqlMap
                                );
                                break;
                            case "SQL SERVER":
                                d1=new ThredQuery(
                                        forName2,
                                        "jdbc:sqlserver://"+terpServer1.getDbServerIp()+":"+terpServer1.getDbServerPort()+";databaseName="+terpServer1.getDbDataBaseName()+";",
                                        "SELECT name FROM " + terpServer.getDbDataBaseName() + "..SysObjects Where XType='U' ORDER BY name",
                                        root,
                                        pwd,
                                        sqlMap
                                );
                                break;
                            case "ORACLE":
                                d1=new ThredQuery(
                                        forName3,
                                        "jdbc:oracle:thin:@"+terpServer1.getDbServerIp()+":"+terpServer1.getDbServerPort()+":"+terpServer1.getDbDataBaseName(),
                                        "select t.table_name from user_tables t",
                                        root,
                                        pwd,
                                        sqlMap
                                );
                                break;
                        }

                        //执行
                        t1 = new Thread(d1);
                        t1.start();
                        Vector<Hashtable<String, Object>> vhso = d1.getVhso();
                        //关闭线程
                        try {
                            if (t1!=null){
                                t1.join();
                            }
                        } catch (InterruptedException e1) {
                            e1.printStackTrace();
                        }

                        for (Map map : vhso) {
                            Map map2 = new HashMap();
                            map2.put("id", terpServers.get(0).getServerId());
                            String TABLE_NAME=(String)map.get("TABLE_NAME");
                            String name=(String)map.get("name");
                            map2.put("label",TABLE_NAME!=null ? TABLE_NAME : name);
                            map2.put("isLeaf", true); //false有子级
                            list.add(map2);
                        }
                    }
                }
            }
            if (list.size()>0){
                toJson.setMsg("true");
                toJson.setFlag(0);
                toJson.setObject(list);
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }


    /**
     * 删除数据源配置
     * @param serverId
     * @return
     */
    @RequestMapping(value = "/deleteTerpServer")
    @ResponseBody
    public ToJson deleteTerpServer(@RequestParam(value = "serverId") Integer serverId){
        ToJson toJson = new ToJson();
        boolean bo = terpServerService.deleteTerpServer(serverId);
        if(bo = true){
            toJson.setFlag(0);
            toJson.setMsg("ok");
        }else {
            toJson.setFlag(1);
            toJson.setMsg("no");
        }
        return toJson;
    }

    /**
     * 数据源连接测试
     * @param terpServer
     * @return
     */
    @RequestMapping(value = "/TerpServerConnectionTest")
    @ResponseBody
    public ToJson TerpServerConnectionTest(TerpServer terpServer) throws SQLException {
        ToJson toJson = new ToJson(1,"false");
        Connection connection=null;
        try {
            switch (terpServer.getDbmsType()){
                case "MYSQL":
                    Class.forName(forName);
                    connection = DriverManager.getConnection(
                            "jdbc:mysql://"+terpServer.getDbServerIp()+":"+terpServer.getDbServerPort()+"/"+terpServer.getDbDataBaseName()+
                                    "?useUnicode=true&characterEncoding="+terpServer.getDbDataBaseCharset()+"&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true",
                            terpServer.getDbServerUser(),
                            terpServer.getDbServerPass()
                    );
                    break;
                case "SQL SERVER":
                    Class.forName(forName2);
                    connection = DriverManager.getConnection(
                            "jdbc:sqlserver://"+terpServer.getDbServerIp()+":"+terpServer.getDbServerPort()+";databaseName="+terpServer.getDbDataBaseName()+";",
                            terpServer.getDbServerUser(),
                            terpServer.getDbServerPass()
                    );
                    break;
                case "ORACLE":
                    Class.forName(forName3);
                    connection = DriverManager.getConnection(
                            "jdbc:oracle:thin:@"+terpServer.getDbServerIp()+":"+terpServer.getDbServerPort()+":"+terpServer.getDbDataBaseName(),
                            terpServer.getDbServerUser(),
                            terpServer.getDbServerPass()
                    );
                    break;
            }

            toJson.setMsg("true");
            toJson.setFlag(0);
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }finally {
            connection.close();
        }
        return toJson;
    }

    /**
     * 显示当前数据源下所有数据库名
     * @param serverId
     * @return
     */
    @RequestMapping(value = "/TerpServerShowDatabases")
    @ResponseBody
    public ToJson TerpServerShowDatabases(@RequestParam(value = "serverId") Integer serverId){
        ToJson toJson = new ToJson();
        TerpServer terpServer =terpServerService.TerpServerShowDatabasesByServerId(serverId);
        //System.out.println("===============terpServer:=================="+terpServer+"==================================");
        String dbmsType = terpServer.getDbmsType();
        String dbServerIp = terpServer.getDbServerIp();
        String dbServerPort = terpServer.getDbServerPort();
        String dbServerUser = terpServer.getDbServerUser();
        String dbServerPass = terpServer.getDbServerPass();
        String driverClassName = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://" + dbServerIp + ":" + dbServerPort;
        String name = dbServerUser;
        String password = dbServerPass;
        if (dbmsType.equalsIgnoreCase("mysql")){
            try {
                Class.forName(driverClassName);
                Connection connection = DriverManager.getConnection(url,name,password);
                if (connection != null){
                    List list = terpServerService.TerpServerShowDatabases();
                    if (list != null){
                        toJson.setObj(list);
                        toJson.setFlag(0);
                        toJson.setMsg("ok");
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }else if (dbmsType.equalsIgnoreCase("oracle")){
            toJson.setFlag(1);
            toJson.setMsg("no");
        }else if (dbmsType.equalsIgnoreCase("sql_server")){
            toJson.setFlag(1);
            toJson.setMsg("no");
        }else {
            toJson.setFlag(1);
            toJson.setMsg("no");
        }
        return toJson;
    }

    /**
     * 显示当前数据库下所有表名
     * @param databaseName
     * @return
     */
    @RequestMapping(value = "/TerpServerShowAllTablesName")
    @ResponseBody
    public ToJson TerpServerShowAllTablesName(@RequestParam(value = "databaseName") String databaseName){
        ToJson toJson = new ToJson();
        List list = terpServerService.TerpServerShowAllDatabaseName(databaseName);
        if(list != null){
            toJson.setObj(list);
            toJson.setFlag(0);
            toJson.setMsg("ok");
        }else {
            toJson.setFlag(1);
            toJson.setMsg("no");
        }
        return toJson;
    }

    /**
     * 显示表结构
     * @param tableName
     * @return
     */
    @RequestMapping(value = "/ShowTableStructure")
    @ResponseBody
    public ToJson ShowTableStructure(@RequestParam(value = "tableName") String tableName){
        ToJson toJson = new ToJson();
        List list = null;
                //terpServerService.ShowTableStructure(tableName);
        if (list != null){
            toJson.setObj(list);
            toJson.setFlag(0);
            toJson.setMsg("ok");
        }
        return toJson;
    }


    /*
     * 创建作者:   廉立深
     * 创建日期:   10:12 2019/12/20
     * 方法介绍:   外部数据源编辑
     * 参数说明:   [terpServer]
     * @return     com.xoa.util.ToJson
     */
    @RequestMapping("/editTerp")
    @ResponseBody
    public ToJson editTerpServer(TerpServer terpServer){
        return terpServerService.editTerpServer(terpServer);
    }

    //👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆👆
    /////////////////////////////////////////////////外部数据源/////////////////////////////////////////////////////////
    /////////////////////////////////////////////////内部数据源/////////////////////////////////////////////////////////
    //👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇👇


    //编辑里的删除字段接口,  传递keyId
    @RequestMapping("/delField")
    @ResponseBody
    public ToJson delField(HttpServletRequest request ,
                           @RequestParam("tableName") String tableName,
                           @RequestParam("fieldName") String fieldName ){
        ToJson toJson=new ToJson(1,"false");
        try {
            String firstDBSource = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");


            Map map=new HashMap();
            //判断库里是否存在这张表,如果存在直接添加
            String dName = "data_"+tableName;
            String dbName = terpServerService.findDbName( firstDBSource , dName );
            if (!StringUtils.checkNull(dbName) && dbName.equals(dName)){
                map.put("tableName" , dbName); //表名称
            }

            String dbTabName = terpServerService.findDbTabName( firstDBSource , dbName, fieldName );
            if (StringUtils.checkNull(dbTabName) || !dbTabName.equals( fieldName )){
                map.put("fieldName" , dbTabName);
            }

            int delField= terpServerService.delField(map);
            if (delField>0){
                toJson.setFlag(0);
                toJson.setMsg("true");
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }

    //根据DID查询内部数据源，返回查询的表结构和内部数据源信息
    @RequestMapping("/findByDataSrcId")
    @ResponseBody
    public ToJson findByDataSrcId(HttpServletRequest request,@RequestParam("dId") Integer dId){
        ToJson toJson=new ToJson(1,"false");
        try {
            Map map=new HashMap();
            map.put("dId",dId);
            DataSrc dataSrc= terpServerService.findByDataSrcId(request,map);
            toJson.setFlag(0);
            toJson.setMsg("true");
            toJson.setObject(dataSrc);
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }


    //查询单个表结构返回字段，
    @RequestMapping("/getTabField")
    @ResponseBody
    public ToJson getTabField(HttpServletRequest request,@RequestParam(value = "tableName",required = true) String tableName){
        return terpServerService.getTabField(request,tableName);
    }


    //判断表是否存在
    @RequestMapping("/countTable")
    @ResponseBody
    public ToJson countTable(HttpServletRequest request,@RequestParam(value = "tableName") String tableName){
        ToJson toJson=new ToJson(1,"false");
        try {
            String firstDBSource = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");
            Map map=new HashMap();
            map.put("tableName","data_"+tableName);
            map.put("tableSchema",firstDBSource);
            int i = terpServerService.countTable(map);
            if (i>0){
                toJson.setFlag(0);
                toJson.setMsg("true");
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }



    /**
     * 新建数据源 数据库创建表
     * 向新建的表中添加dDesc
     * 并添加到data_src中
     * @param tables 要创建的字段
     * @param dataSrc 要新增的数据源
     * @return
     */
    @RequestMapping(value = "/CreateTerpServerTable")
    @ResponseBody
    @Transactional
    public ToJson CreateTerpServerTable(HttpServletRequest request,@RequestParam(value = "tables") String tables
            ,DataSrc dataSrc){
        ToJson toJson = new ToJson(1,"false");
        try {
            Cookie redisSessionId = CookiesUtil.getCookieByName(request, "redisSessionId");
            Users loginUser = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(),redisSessionId);
            String firstDBSource = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");
            String str1 ="[0-9a-zA-Z_]{1,9}";



            Map map = new HashMap();

            //判断库里是否存在这张表,如果存在直接添加
            String dName = "data_" + dataSrc.getdName();
            String dbName = terpServerService.findDbName( firstDBSource , dName );
            if (!StringUtils.checkNull(dbName) && dbName.equals(dName)){
                map.put("dName" , dbName); //表名称
            }

            map.put( "dDesc" , dataSrc.getdDesc() ); //表描述

            int i = 0;
            List<DataSrc> data = JSON.parseArray(tables,DataSrc.class);
            List<DataSrc> dataSrcs =new ArrayList<>();
            for (DataSrc dataSrc1 : data) {
                String s = dataSrc1.getData1().replaceAll(" ", "");
                if (str1.matches(s)) {
                    dataSrc1.setData1( s );
                }


                switch (dataSrc1.getData2()){
                    case "int":
                    case "varchar":
                        break;
                    default:
                        break;
                }
                dataSrcs.add(dataSrc1);
                i++;
            }
            map.put( "table" , dataSrcs ); //表字段

            map.put( "firstDBSource" , firstDBSource ); //库

            boolean bo; //判断是否创建表
            boolean bo1= false; //判断是否创建数据源
            if(dataSrc.getdId()!=null && !StringUtils.checkNull(dataSrc.getdId().toString())){ //判断是否传递ID主键，未传递进行新增操作
                //修改表结构
                bo=terpServerService.ALTERDataSrcTable(map);
            }else{
                map.put("dName" , dName);
                //创建表结构
                bo=terpServerService.CreateTerpServerTable(map);
            }

            //新增成功进行添加表结构
            if (bo){
                dataSrc.setdEditor(loginUser.getUserId()); //修改人
                dataSrc.setdEditTime(new Date()); //修改时间

                if(dataSrc.getdId()!=null && !StringUtils.checkNull(dataSrc.getdId().toString())){ //判断是否传递ID主键，未传递进行新增操作
                    //编辑数据源信息
                    bo1 = terpServerService.updataTerpServerById(dataSrc);
                }else{

                    dataSrc.setdDept(loginUser.getDeptId().toString()); //数据源创建部门
                    dataSrc.setdCreateTime(new Date()); //创建时间
                    dataSrc.setdPriv(""); //权限
                    dataSrc.setdCreator(loginUser.getUserId()); //创建人
                    dataSrc.setdModuleName("");
                    //添加数据
                    bo1 = terpServerService.insertDataSrcNewtable(dataSrc);

                }
            }

            //添加完表结构返回状态
            if(bo1){
                toJson.setFlag(0);
                toJson.setMsg("ok");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return toJson;
    }

    /**
     * 内部数据源条件查询  like
     * @param dName
     * @param dDesc
     * @param page
     * @param pageSize
     * @return
     */
    @RequestMapping(value = "/selectTerpServerByLike")
    @ResponseBody
    public ToJson selectTerpServerByLike(HttpServletRequest request,
                                         @RequestParam(value = "dName",required = false)String dName,
                                         @RequestParam(value = "dDesc",required = false)String dDesc,
                                         @RequestParam(value = "page" ,required = false )Integer page,
                                         @RequestParam(value = "limit" ,required = false )Integer pageSize){
        ToJson toJson = new ToJson(1,"暂无数据");
        Map map = new HashMap();
        try {
            PageParams pageParams = new PageParams();
            pageParams.setPage(page);
            pageParams.setPageSize(pageSize);
            pageParams.setUseFlag(true);
            map.put("pageParams",pageParams);
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        try {
            String firstDBSource = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");
            map.put("dName",dName);
            map.put("dDesc",dDesc);
            Map tbmap=new HashMap();
            List<DataSrc> list = terpServerService.selectTerpServerByLike(map);
            for (DataSrc ds:list) {
                tbmap.put("tableSchema",firstDBSource);
                tbmap.put("tableName","data_"+ds.getdName());
                DataSrc tableSize = terpServerService.findTableSize(tbmap);
                if (tableSize!=null && tableSize.getDataRow()!=null && tableSize.getDatasize()!=null){
                    ds.setDataRow(tableSize.getDataRow());
                    ds.setDatasize(tableSize.getDatasize());
                }

                if (!StringUtils.checkNull(ds.getdPriv())){
                    String userNamesByUserIds = usersService.getUserNamesByUserIds(ds.getdPriv());
                    if (!StringUtils.checkNull(userNamesByUserIds)){
                        ds.setdPrivNames(userNamesByUserIds);
                    }
                }
            }
            if (list.size()>0){
                toJson.setObj(list);
                PageParams pageParams=(PageParams)map.get("pageParams");
                toJson.setTotleNum(pageParams.getTotal());
                toJson.setFlag(0);
                toJson.setMsg("ok");
                toJson.setCode("0");
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }

    /**
     * 删除内部展示数据源
     * @param dID
     * @return
     */
    @RequestMapping(value = "/deleteTerpServerTwo")
    @ResponseBody
    public ToJson deleteTerpServerTwo(@RequestParam(value = "dID") Integer dID){
        ToJson toJson = new ToJson(1,"false");
        int delTable=terpServerService.deleteTable(dID); //先删表
        if (delTable==0){
            boolean bo = terpServerService.deleteTerpServerTwo(dID);
            if(bo = true){
                toJson.setMsg("未找到相关表结构");
            }else{
                toJson.setMsg("删除成功");
            }
            toJson.setFlag(0);
        }
        return toJson;
    }

    /**
     * 浏览当前点击数据源,创建表的信息
     * 公共查询方法 不仅仅在浏览里使用
     * @param dName 要查找的表
     * @param status 判断状态是否要加上data_
     * @param strjson 接收条件查询的参数
     * @return
     */
    @RequestMapping(value = "/selectTerpServerBydID")
    @ResponseBody
    public ToJson selectTerpServerBydID(HttpServletRequest request,
                                        @RequestParam(value = "dName") String dName,
                                        @RequestParam(value = "page" ,required = false) Integer page,
                                        @RequestParam(value = "limit",required = false) Integer limit,
                                        @RequestParam(value = "status",required = false) boolean status,
                                        @RequestParam(value = "strjson",required = false) String strjson
    ){
        ToJson toJson = new ToJson(1,"false");
        Map map=new HashMap();
        try {
            PageParams pageParams=new PageParams();
            pageParams.setPage(page);
            pageParams.setPageSize(limit);
            pageParams.setUseFlag(true);
            map.put("pageParams",pageParams);
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        try {
            String sqlType = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");

            if (!status){
                dName = "data_" + dName;
            }

            //判断库里是否存在这张表,如果存在直接添加
            String dbName = terpServerService.findDbName(sqlType, dName);
            if (!StringUtils.checkNull(dbName) && dbName.equals(dName)){
                map.put("tableName",dbName);
            }


            //解析json对象
            if (!StringUtils.checkNull(strjson)){
                Map maps = (Map)JSON.parse(strjson);

                Iterator<Map.Entry<String, String>> it = map.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry<String, String> entry = it.next();
                    String dbTabName = terpServerService.findDbTabName(sqlType, dbName, (String) entry.getKey());
                    if (StringUtils.checkNull(dbTabName) || !dbTabName.equals((String) entry.getKey())){
                        maps.remove((String) entry.getKey());
                    }
                }

                map.put("condition" , maps );
            }

            //公共查询表方法返回全部信息，传入表名
            List<Map<String,Object>> maps= terpServerService.selectAll(map);
            if (maps.size()>0){
                toJson.setObj(maps);
                toJson.setFlag(0);
                toJson.setMsg("true");
                PageParams pageParams=(PageParams) map.get("pageParams");
                toJson.setTotleNum(pageParams.getTotal());
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }


    /**
     * 浏览里的 新增 和 编辑 接口
     * @param strjson 传递要新增的数据集因为不能创建对象只能接收list类型
     * @param tableName 表名
     * @return
     */
    @RequestMapping(value = "/insertData")
    @ResponseBody
    public ToJson insertData(HttpServletRequest request,
                             @RequestParam("strjson") String strjson,
                             @RequestParam("tableName") String tableName){
        ToJson toJson = new ToJson();
        Map map = new HashMap();
        try {
            String sqlType = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");

            //判断库里是否存在这张表
            String dbName = terpServerService.findDbName(sqlType, "data_"+tableName);
            if (!StringUtils.checkNull(dbName) && dbName.equals("data_"+tableName)){
                map.put("tableName",dbName);
            }


            Map maps = (Map)JSON.parse(strjson);
            Object ds_key_id = maps.get("ds_key_id");
            Iterator<Map.Entry<String, String>> it = map.entrySet().iterator();
            while (it.hasNext()) {
                Map.Entry<String, String> entry = it.next();
                String dbTabName = terpServerService.findDbTabName(sqlType, dbName, (String) entry.getKey());
                if (StringUtils.checkNull(dbTabName) || !dbTabName.equals((String) entry.getKey())){
                    maps.remove((String) entry.getKey());
                }
            }
            map.put("tabObj",maps);



            boolean bo;
            if (ds_key_id!=null){
                bo=terpServerService.updateData(map);
            }else {
                bo = terpServerService.insertData(map);
            }
            if (bo){
                toJson.setFlag(0);
                toJson.setMsg("ok");
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }

    /**
     * 向内部数据源中添加字段
     * @param tableName
     * @param fieldName
     * @param fieldType
     * @param onlyYesAndNo
     * @return  暂时没有用到  如果发现有用到这个接口  请考虑一下sql注入问题
     */
  /*  @RequestMapping(value = "/alterTableField")
    @ResponseBody
    public ToJson alterTableField(HttpServletRequest request,
                                  @RequestParam(value = "tableName")String tableName,
                                  @RequestParam(value = "fieldName") String fieldName,
                                  @RequestParam(value = "fieldComment") String fieldComment,
                                  @RequestParam(value = "fieldType") String fieldType,
                                  @RequestParam(value = "onlyYesAndNo",required = false) String onlyYesAndNo){
        ToJson toJson = new ToJson();
        boolean bo;
        Map map = new HashMap();
        String sqlType = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");

        //判断库里是否存在这张表
        String dbName = terpServerService.findDbName(sqlType, tableName);
        if (!StringUtils.checkNull(dbName) && dbName.equals(tableName)){
            map.put("tableName",dbName);
        }

        map.put("fieldName",fieldName.replaceAll("'",""));

        if (fieldType.equalsIgnoreCase("Varchar")){
            map.put("fieldComment",fieldComment);
            map.put("fieldSize" , "200");
            map.put("fieldType",fieldType);
            bo = terpServerService.alterTableField(map);
            if (bo = true){
                toJson.setFlag(0);
                toJson.setMsg("ok");
            }
        }else if (fieldType.equalsIgnoreCase("int")){
            map.put("fieldComment",fieldComment);
            map.put("fieldSize" , "40");
            map.put("fieldType",fieldType);
            if(onlyYesAndNo.equalsIgnoreCase("yes")){
                bo = terpServerService.alterTableField(map);
                if (bo = true){
                    boolean bo1 = terpServerService.onlyYesAndNo(map);
                    if (bo1 = true){
                        toJson.setFlag(0);
                        toJson.setMsg("ok");
                    }
                }
            }else {
                bo = terpServerService.alterTableField(map);
                if (bo = true){
                    toJson.setFlag(0);
                    toJson.setMsg("ok");
                }
            }

        }

        return toJson;
    }*/

    /**
     * 浏览里的编辑操作
     * @param tableName
     * @param oldField
     * @param newField
     * @param fieldType
     * @param fieldSize
     * @param fieldComment
     * @return
     */
/*    @RequestMapping(value = "/updataTerpServerField")
    @ResponseBody
    public ToJson updataTerpServerField(@RequestParam(value = "tableName")String tableName,
                                        @RequestParam(value = "oldField")String oldField,
                                        @RequestParam(value = "newField")String newField,
                                        @RequestParam(value = "fieldType")String fieldType,
                                        @RequestParam(value = "fieldSize")String fieldSize,
                                        @RequestParam(value = "fieldComment")String fieldComment){
        ToJson toJson = new ToJson();
        Map map = new HashMap();
        map.put("tableName",tableName);
        map.put("oldField",oldField);
        map.put("newField",newField);
        map.put("fieldType",fieldType);
        map.put("fieldSize",fieldSize);
        map.put("fieldComment",fieldComment);
        boolean bo = terpServerService.updataTerpServerField(map);
        if (bo = true){
            toJson.setFlag(0);
            toJson.setMsg("ok");
        }
        return toJson;
    }*/

    /**
     * 浏览 里的删除 操作 ,
     * @param tableName 传入表名
     * @param keyId 传入要删除的ID
     * @return
     */
    @RequestMapping(value = "/dropTableField")
    @ResponseBody
    public ToJson dropTableField(HttpServletRequest request,
                                 @RequestParam(value = "tableName")String tableName,
                                 @RequestParam(value = "keyId")String keyId){
        ToJson toJson = new ToJson();
        String sqlType = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");

        Map map = new HashMap();
        //判断库里是否存在这张表
        String dbName = terpServerService.findDbName(sqlType, "data_"+tableName);
        if (!StringUtils.checkNull(dbName) && dbName.equals("data_"+tableName)){
            map.put("tableName",dbName);
        }
        map.put("keyId",keyId);
        boolean bo = terpServerService.dropTableField(map);
        if (bo){
            toJson.setFlag(0);
            toJson.setMsg("ok");
        }
        return toJson;
    }

    /**
     * 清空表中所有信息
     * @param tableName
     * @return
     */
    @RequestMapping(value = "/delectTableAllData")
    @ResponseBody
    public ToJson delectTableAllData(HttpServletRequest request,
            @RequestParam(value = "tableName") String tableName
    ){
        String sqlType = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");

        ToJson toJson = new ToJson();

        //判断库里是否存在这张表
        String dbName = terpServerService.findDbName(sqlType, "data_"+tableName);
        if (!StringUtils.checkNull(dbName) && dbName.equals("data_"+tableName)){
           terpServerService.delectTableAllData(dbName);
            toJson.setFlag(0);
            toJson.setMsg("ok");
        }
        return toJson;
    }



    /**
     * 添加管理员
     * @param dId 主键ID
     * @param dPriv  权限人
     * @return
     */
    @RequestMapping(value = "/addAdministrators")
    @ResponseBody
    public ToJson addAdministrators(@RequestParam(value = "dId")Integer dId,
                                    @RequestParam(value = "dPriv")String dPriv){
        ToJson toJson = new ToJson();
        DataSrc dataSrc=new DataSrc();
        dataSrc.setdId(dId);
        dataSrc.setdPriv(dPriv);
        boolean bo = terpServerService.addAdministrators(dataSrc);
        if (bo){
            toJson.setFlag(0);
            toJson.setMsg("ok");
        }
        return toJson;
    }

    /**
     * 导出
     * @param tableName 传入表名
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "/terpServerExportExcel")
    @ResponseBody
    public ToJson terpServerExportExcel(@RequestParam(value = "tableName") String tableName,
                                        HttpServletRequest request, HttpServletResponse response){
        ToJson toJson = new ToJson();
        String firstDBSource = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");

        //excel的文档对象
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //excel的表单
        HSSFSheet hssfSheet = hssfWorkbook.createSheet(tableName+"的数据表");
        //excel的行
        HSSFRow hssfRow = hssfSheet.createRow(0);
        //创建单元格
        HSSFCell hssfCell = hssfRow.createCell(0);
        //设置单元格内容
        //hssfCell.setCellValue(tableName+"表的数据");
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        //hssfSheet.addMergedRegion(new CellRangeAddress(0,0,0,100));
        //创建第二行
        HSSFRow hssfRow2 = hssfSheet.createRow(0);
        Map map = new HashMap();
        map.put("tableName","data_"+tableName);
        map.put("tableSchema",firstDBSource );
        List<String> list = terpServerService.selectOneTableFieldByTableName(map);
        for(int i=0;i<list.size();i++){
            String str = list.get(i).toString().replaceAll("_","");
            //System.out.println("========================================="+str+"=============================");
            hssfRow2.createCell(i).setCellValue(str);
        }
        //创建第三行及以后的行
        //list1  数据内容
        List<Map<String,String>> list1 = terpServerService.selectOneTableByTableName((String) map.get("tableName"));
        //System.out.println("========================================="+list1+"=============================");
        //integer 数据条数
        Integer integer = list1.size();
        for (int j=1;integer>=j;j++) {
            HSSFRow hssfRow3 = hssfSheet.createRow(j);
            //获取list中一条数据
            int i = j-1;
            Map map1 = list1.get(i);
            for (int p = 0; p < list.size(); p++) {
                //数据库的字段名
                String string = list.get(p);
                Object object = map1.get(string);
                if (object.equals(null)){
                    String str = "null";
                    hssfRow3.createCell(p).setCellValue(str);
                }else if (object.getClass().getName().equalsIgnoreCase("Date")){
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                    String str = simpleDateFormat.format(object);
                    hssfRow3.createCell(p).setCellValue(str);
                }else if (object.getClass().getName().equalsIgnoreCase("SimpleDateFormat")){
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                    String str = simpleDateFormat.format(object);
                    hssfRow3.createCell(p).setCellValue(str);
                }else if (object.getClass().getName().equalsIgnoreCase("Calendar")){
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                    String str = simpleDateFormat.format(object);
                    hssfRow3.createCell(p).setCellValue(str);
                }else if (object.equals("")){
                    String str = "null";
                    hssfRow3.createCell(p).setCellValue(str);
                }else {
                    String str = object.toString();
                    hssfRow3.createCell(p).setCellValue(str);
                }
            }
        }
        try {
            OutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+tableName+".xls");
            response.setContentType("application/msexcel");
            hssfWorkbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }

    //导入
    @RequestMapping(value = "/terpServerImportExcel")
    @ResponseBody
    public ToJson terpServerImportExcel(HttpServletRequest request, HttpServletResponse response, MultipartFile file, HttpSession session,String tableName){
        ToJson toJson = null;
        try {
            toJson=terpServerService.terpServerImportExcel(request,response,file,session,tableName);
            response.setHeader("content-type", "text/html;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            ServletOutputStream out = response.getOutputStream();
            OutputStreamWriter ow = new OutputStreamWriter(out, "UTF-8");
            ow.write(JSONObject.toJSONString(toJson));
            ow.flush();
            ow.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return toJson;
    }
}
